package edu.uoc.tdp.pac3.comun;

import java.rmi.RemoteException;
import java.rmi.server.UnicastRemoteObject;
import java.sql.*;
import java.util.ArrayList;

public class GestorSugerenciasImpl 
extends UnicastRemoteObject
implements GestorSugerenciasInterface
{
	private static final long serialVersionUID = 1L;
	
	private Connection bbddConnection;
	
	public GestorSugerenciasImpl(Connection con) throws RemoteException 
	{
		super();

		bbddConnection = con;
	}
	
	/**
	 * Devuelve una lista de todos los empleados.
	 */
	public ArrayList<Empleado> getListaEmpleados() throws RemoteException, GestionSugerenciasException
	{
		ArrayList<Empleado> lista = new ArrayList<Empleado>();
		
		Statement st = null;
		ResultSet rs = null;
		try 
		{
			st = bbddConnection.createStatement();
			rs = st.executeQuery("select * from tb_employee");
			while( rs.next() )
			{
				Empleado e = new Empleado(rs.getInt("employee_id"), 
										rs.getInt("mall_id"),
										rs.getString("employee_name"),
										rs.getDate("creation_date") );
				
				lista.add( e );
			}
		} 
		catch (SQLException e) 
		{
			String exText = GestionSugerenciasException.ERROR_READING_BBDD + "\n\n" + e.getMessage();
			throw new GestionSugerenciasException(exText);
		}
		finally
		{
			try
			{
				if (rs != null)
					rs.close();

				if (st != null)
					st.close();
			}
			catch(SQLException ex)
			{
				String exText = GestionSugerenciasException.ERROR_READING_BBDD + "\n\n" + ex.getMessage();
				throw new GestionSugerenciasException(exText);
			}
		}
		
		return lista;
	}
	
	/**
	 * Devuelve una lista de todos los establecimientos.
	 */
	public ArrayList<Establecimiento> getListaEstablecimientos() throws RemoteException, GestionSugerenciasException
	{
		ArrayList<Establecimiento> lista = new ArrayList<Establecimiento>();

		Statement st = null;
		ResultSet rs = null;
		try 
		{
			st = bbddConnection.createStatement();
			rs = st.executeQuery("select * from tb_mall");
			while( rs.next() )
			{
				Establecimiento e = new Establecimiento( rs.getInt("mall_id"), 
														rs.getString("mall_name"),
														rs.getString("mall_city"),
														rs.getDate("creation_date") );
				
				lista.add( e );
			}
		} 
		catch (SQLException e) 
		{
			String exText = GestionSugerenciasException.ERROR_READING_BBDD + "\n\n" + e.getMessage();
			throw new GestionSugerenciasException(exText);
		}
		finally
		{
			try
			{
				if (rs != null)
					rs.close();

				if (st != null)
					st.close();
			}
			catch(SQLException ex)
			{
				String exText = GestionSugerenciasException.ERROR_READING_BBDD + "\n\n" + ex.getMessage();
				throw new GestionSugerenciasException(exText);
			}
		}
		
		return lista;
	}
	
	/**
	 * Devuelve una lista de todas las sugerencias.
	 */
	public ArrayList<Sugerencia> getListaTitulos() throws RemoteException, GestionSugerenciasException
	{
		ArrayList<Sugerencia> lista = new ArrayList<Sugerencia>();

		Statement st = null;
		ResultSet rs = null;
		try 
		{
			st = bbddConnection.createStatement();
			rs = st.executeQuery("select * from tb_suggestion");
			while( rs.next() )
			{
				Sugerencia s = new Sugerencia( rs.getInt("suggestion_id"),
												rs.getInt("mall_id"),
												rs.getInt("employee_id"),
												rs.getString("description"),
												rs.getDate("creation_date"),
												rs.getString("is_suggestion_closed") );
				lista.add( s );
			}
		} 
		catch (SQLException e) 
		{
			String exText = GestionSugerenciasException.ERROR_READING_BBDD + "\n\n" + e.getMessage();
			throw new GestionSugerenciasException(exText);
		}
		finally
		{
			try
			{
				if (rs != null)
					rs.close();

				if (st != null)
					st.close();
			}
			catch(SQLException ex)
			{
				String exText = GestionSugerenciasException.ERROR_READING_BBDD + "\n\n" + ex.getMessage();
				throw new GestionSugerenciasException(exText);
			}
		}
			
		return lista;
	}
	
	/**
	 * Graba una nueva sugerencia en la base de datos.
	 */
	public int altaNuevaSugerencia(Sugerencia s) throws RemoteException, GestionSugerenciasException
	{
		Statement st = null;
		
		try 
		{
			bbddConnection.setAutoCommit( false );
			
			st = bbddConnection.createStatement();
			String insert = "insert into tb_suggestion(employee_id, mall_id, creation_date, description, is_suggestion_closed)"
					+ " values (" 
					+ String.valueOf(s.getIdEmpleado()) + ","
					+ String.valueOf(s.getIdEstablecimiento()) + ","
					+ "to_timestamp('" + s.getDataAltaSugerencia() + "','YYYY-MM-DD HH24:MI'),"
					+ "'" + s.getDescripcion() + "',"
					+ "'" + (s.getCerrada().equals("N") ? "N" : "Y") + "')";
			
			st.executeUpdate(insert, Statement.RETURN_GENERATED_KEYS);
			ResultSet rs = st.getGeneratedKeys();
			if (rs.next())
				s.setIdSugerencia( rs.getInt("suggestion_id") );
			bbddConnection.commit();
		} 
		catch (SQLException e) 
		{
			try
			{
				bbddConnection.rollback();
			}
			catch(SQLException ex)
			{ }
			
			String exText = GestionSugerenciasException.ERROR_WRITING_BBDD + "\n\n" + e.getMessage();
			throw new GestionSugerenciasException(exText);
		}
		finally
		{
			try
			{
				if (st != null)
					st.close();
			}
			catch(SQLException ex)
			{
				String exText = GestionSugerenciasException.ERROR_WRITING_BBDD + "\n\n" + ex.getMessage();
				throw new GestionSugerenciasException(exText);
			}
		}
		
		return s.getIdSugerencia();
	}

	/**
	 * Graba un nuevo apunte en la base de datos.
	 */
	public void GrabaApunte(Apunte a) throws RemoteException, GestionSugerenciasException 
	{
		Statement st = null;
		
		try 
		{
			bbddConnection.setAutoCommit( false );
			
			st = bbddConnection.createStatement();
			String insert = "insert into tb_suggestion_note(suggestion_id, employee_id, creation_date, description)"
					+ " values (" 
					+ String.valueOf(a.getIdSugerencia()) + ","
					+ String.valueOf(a.getIdEmpleado()) + ","
					+ "to_timestamp('" + a.getFechaApunte() + "','YYYY-MM-DD HH24:MI'),"
					+ "'" + a.getDescripcion() + "')";
			st.executeUpdate(insert, Statement.RETURN_GENERATED_KEYS);
			ResultSet rs = st.getGeneratedKeys();
			if (rs.next())
				a.setIdApunte( rs.getInt("suggestion_note_id") );
			
			bbddConnection.commit();
		} 
		catch (SQLException e) 
		{
			try
			{
				bbddConnection.rollback();
			}
			catch(SQLException ex)
			{ }
			
			String exText = GestionSugerenciasException.ERROR_WRITING_BBDD + "\n\n" + e.getMessage();
			throw new GestionSugerenciasException(exText);
		}
		finally
		{
			try
			{
				if (st != null)
					st.close();
			}
			catch(SQLException ex)
			{
				String exText = GestionSugerenciasException.ERROR_WRITING_BBDD + "\n\n" + ex.getMessage();
				throw new GestionSugerenciasException(exText);
			}
		}
	}

	/**
	 * Devuelve una lista con las descrpciones de todas las sugerencias.
	 */
	public ArrayList<String> getListaSugerencias() throws RemoteException, GestionSugerenciasException 
	{
		ArrayList<String> lista = new ArrayList<String>();

		Statement st = null;
		ResultSet rs = null;
		
		try 
		{
			st = bbddConnection.createStatement();
			rs = st.executeQuery("select suggestion_id,creation_date,description from tb_suggestion");
			
			while( rs.next() )
			{
				String s = "#"+rs.getInt("suggestion_id")+" ("+rs.getDate("creation_date").toString()+" - "+rs.getString("description")+") ";
				lista.add( s );
			}
		} 
		catch (SQLException e) 
		{
			String exText = GestionSugerenciasException.ERROR_READING_BBDD + "\n\n" + e.getMessage();
			throw new GestionSugerenciasException(exText);
		}
		finally
		{
			try
			{
				if (rs != null)
					rs.close();

				if (st != null)
					st.close();
			}
			catch(SQLException ex)
			{
				String exText = GestionSugerenciasException.ERROR_READING_BBDD + "\n\n" + ex.getMessage();
				throw new GestionSugerenciasException(exText);
			}
		}
			
		return lista;
	}

	public ArrayList<String> getConsultaSugerencias() throws RemoteException, GestionSugerenciasException 
	{
		// TODO Auto-generated method stub
		return null;
	}
	
	/**
	 * Devuelve una lista de todas las noteas de una sugerencia.
	 */
	public ArrayList<Apunte> getApuntesSugerencia(int idSugerencia) throws RemoteException, GestionSugerenciasException
	{
		ArrayList<Apunte> lista = new ArrayList<Apunte>();

		Statement st = null;
		ResultSet rs = null;
		try 
		{
			st = bbddConnection.createStatement();
			rs = st.executeQuery("select a.*, e.employee_name "
					+ "from tb_suggestion_note as a "
					+ "join tb_employee as e on e.employee_id = a.employee_id "
					+ "where suggestion_id = " + String.valueOf(idSugerencia));
			while( rs.next() )
			{
				String s = rs.getString("description");
				Apunte a = new Apunte( rs.getInt("suggestion_note_id"),
									rs.getInt("suggestion_id"),
									rs.getInt("employee_id"),
									rs.getString("description"),
									rs.getDate("creation_date"),
									rs.getString("employee_name"));

				lista.add( a );
			}
		} 
		catch (SQLException e) 
		{
			String exText = GestionSugerenciasException.ERROR_READING_BBDD + "\n\n" + e.getMessage();
			throw new GestionSugerenciasException(exText);
		}
		finally
		{
			try
			{
				if (rs != null)
					rs.close();

				if (st != null)
					st.close();
			}
			catch(SQLException ex)
			{
				String exText = GestionSugerenciasException.ERROR_READING_BBDD + "\n\n" + ex.getMessage();
				throw new GestionSugerenciasException(exText);
			}
		}
			
		return lista;
	}
	
	/**
	 * Devuelve el estado de la sugerencia segun su Id
	 */
	public String getCerrada(int idSugerencia) throws RemoteException, GestionSugerenciasException
	{
		Statement st = null;
		ResultSet rs = null;
		String closed = null;
		try 
		{
			st = bbddConnection.createStatement();
			rs = st.executeQuery("select is_suggestion_closed from tb_suggestion where suggestion_id = " + String.valueOf(idSugerencia));
			
		 rs.next();
		 closed = rs.getString("is_suggestion_closed");		
			
		} 
		catch (SQLException e) 
		{
			String exText = GestionSugerenciasException.ERROR_READING_BBDD + "\n\n" + e.getMessage();
			throw new GestionSugerenciasException(exText);
		}
		finally
		{
			try
			{
				if (rs != null)
					rs.close();

				if (st != null)
					st.close();
			}
			catch(SQLException ex)
			{
				String exText = GestionSugerenciasException.ERROR_READING_BBDD + "\n\n" + ex.getMessage();
				throw new GestionSugerenciasException(exText);
			}
		}
			
		return closed;
	}

	/**
	 * Actualizar el indicador de cerrada de una sugerencia.
	 */
	public void ActualizarSugerencia(int sugerencia, String cerrada) throws RemoteException, GestionSugerenciasException 
	{
		Statement st = null;
		
		if (sugerencia == 0)
			return;
		
		if (cerrada.startsWith("N"))
			cerrada = "N";
		else
			cerrada = "Y";
		
		try 
		{
			bbddConnection.setAutoCommit( false );
			
			st = bbddConnection.createStatement();
			String update = "update tb_suggestion set is_suggestion_closed = '" + cerrada + "'"
					+ " where suggestion_id = " + String.valueOf(sugerencia); 
			st.executeUpdate(update);
			
			bbddConnection.commit();
		} 
		catch (SQLException e) 
		{
			try
			{
				bbddConnection.rollback();
			}
			catch(SQLException ex)
			{ }
			
			String exText = GestionSugerenciasException.ERROR_WRITING_BBDD + "\n\n" + e.getMessage();
			throw new GestionSugerenciasException(exText);
		}
		finally
		{
			try
			{
				if (st != null)
					st.close();
			}
			catch(SQLException ex)
			{
				String exText = GestionSugerenciasException.ERROR_WRITING_BBDD + "\n\n" + ex.getMessage();
				throw new GestionSugerenciasException(exText);
			}
		}
	}
}
